﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Stone.Library.DB;
using Stone.DataContracts;
using System.Transactions;

namespace CourseArraging.DAO
{
    public class yhxxDAO
    {
        #region 事务操作

        public void addTecall(TecEntity tecEntity, TecArrangeEntity taEntity, Zap2Entity zap2Entity)
        {
            TransactionOptions option = new TransactionOptions();
            option.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, option))
            {
                try
                {
                    //插入教员基本信息
                    taEntity.tec_info_id = addTecinfo(tecEntity);
                    //插入教员安排信息
                    addTecarrange(taEntity);
                    //插入周安排信息
                    zap2Entity.zapsbid = taEntity.tec_info_id;
                    zap2Entity.zapsbno = "";
                    new Zap2DAO().Add(zap2Entity);
                    scope.Complete();
                }
                finally
                {
                    scope.Dispose();
                }
            }
        }

        public void delTecall(string tecinfo_id)
        {
            string str1 = "delete from tec_info where tec_info_id=" + tecinfo_id + ";delete from zap2 where zap_sb_id=" + tecinfo_id;
            DBExecuteUtil.executeSql(str1);
            //string str1 = "delete from tec_info where tec_info_id=" + tecinfo_id
            //                + ";delete from tec_arrange where tec_info_id=" + tecinfo_id;
            //TransactionOptions option = new TransactionOptions();
            //option.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
            //using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, option))
            //{
            //    try
            //    {
            //        DBExecuteUtil.executeSql(str1);
            //        scope.Complete();
            //    }
            //    finally
            //    {
            //        scope.Dispose();
            //    }
            //}
        }

        public void updateTecall(TecEntity tecEntity, TecArrangeEntity taEntity, Zap2Entity zap2)
        {
            TransactionOptions option = new TransactionOptions();
            option.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, option))
            {
                try
                {
                    updateTecinfo(tecEntity);
                    delTecarrange(tecEntity.user_id.ToString());
                    taEntity.tec_info_id = tecEntity.user_id;
                    addTecarrange(taEntity);
                    new Zap2DAO().Delete(tecEntity.user_id);
                    zap2.zapsbid = tecEntity.user_id;
                    new Zap2DAO().Add(zap2);
                    scope.Complete();
                }
                finally
                {
                    scope.Dispose();
                }
            }
        }
        #endregion

        #region 教师查询
        public DataTable GetTecinfo()
        {
            return GetTecinfo8no_time(null, null);
        }

        public DataTable GetTecinfo8no_time(string tmpno, string time)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT distinct tec_info.tec_info_id 教员流水,tec_info_no 教员编号,tec_info.tec_info_name 姓名,")
                .Append("tec_info.tec_info_course 可教课程,tec_info.tec_info_grade 可教年级,tec_info.tec_info_free_string 上课时间,")
                .Append("tec_info.tec_info_spell 拼音简写, tec_info.tec_info_sex 性别,  tec_info.tec_info_gddh 固定电话,")
                .Append("tec_info.tec_info_phone 手机号码,  tec_info.tec_info_address 家庭地址,tec_info.tec_info_right 权限,")
                .Append("tec_info.tec_info_regedit 注册时间,tec_info.tec_info_dep 分校,tec_info.tec_info_bz 备注 ")
                .Append("FROM tec_info,ZAP2 WHERE TEC_INFO.TEC_INFO_ID=ZAP2.ZAP_SB_ID ");
            if (!string.IsNullOrEmpty(tmpno))
            {
                sb.Append(" and tec_info_no like '%" + tmpno + "%'");
            }
            if (!string.IsNullOrEmpty(time))
            {
                sb.Append(" and " + time + "='√' ");
            }
            return DBExecuteUtil.querySqlTable(sb.ToString());
        }

        public TecEntity GetYhxxEntity8Condi(string spell, string no)
        {
            TecEntity tecEntity;
            string[][] tecEntityArray;
            StringBuilder sb = new StringBuilder("select tec_info_no,tec_info_id,tec_info_name,tec_info_psw,")
                .Append("tec_info_sex,tec_info_phone,tec_info_address,tec_info_identify,tec_info_right,tec_info_spell ")
                .Append("from tec_info where 1=1 ");
            List<SqlParameter> sqlParamsList = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(spell))
            {
                sb.Append(" and tec_info_spell=@tec_info_spell ");
                sqlParamsList.Add(new SqlParameter("@tec_info_spell", spell));
            }
            if (!string.IsNullOrEmpty(no))
            {
                sb.Append(" and tec_info_no=@tec_info_no ");
                sqlParamsList.Add(new SqlParameter("@tec_info_no", no));
            }
            tecEntityArray = DBExecuteUtil.querySqlArray(sb.ToString(), sqlParamsList.ToArray());
            if (tecEntityArray == null || tecEntityArray.Length == 0) return null;
            tecEntity = new TecEntity();
            tecEntity.user_id = int.Parse(tecEntityArray[0][1]);
            tecEntity.user_no = tecEntityArray[0][0];
            tecEntity.user_name = tecEntityArray[0][2];
            tecEntity.user_psw = tecEntityArray[0][3];
            tecEntity.user_sex = tecEntityArray[0][4];
            tecEntity.user_phone = tecEntityArray[0][5];
            tecEntity.user_address = tecEntityArray[0][6];
            tecEntity.user_identify = tecEntityArray[0][7];
            tecEntity.user_right = tecEntityArray[0][8];
            tecEntity.user_spell = tecEntityArray[0][9];
            return tecEntity;
        }

        public TecEntity GetTecinfo8id(string id)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT     tec_info_no, tec_info_id , tec_info_name, tec_info_course, tec_info_grade, ")
                .Append("tec_info_spell, tec_info_sex, tec_info_gddh, tec_info_phone, tec_info_address, ")
                .Append("tec_info_right, tec_info_regedit, tec_info_bz, tec_info_free_string,tec_info_identify ")
                .Append("FROM  dbo.tec_info WHERE tec_info_id=" + id);
            string[][] result = DBExecuteUtil.querySqlArray(sb.ToString());
            if (result == null) return null;
            TecEntity te = new TecEntity();
            te.user_no = result[0][0];
            te.user_id = int.Parse(result[0][1]);
            te.user_name = result[0][2];
            te.user_course = result[0][3];
            te.user_grade = result[0][4];
            te.user_spell = result[0][5];
            te.user_sex = result[0][6];
            te.user_gddh = result[0][7];
            te.user_phone = result[0][8];
            te.user_address = result[0][9];
            te.user_right = result[0][10];
            te.user_regedit = result[0][11];
            te.user_bz = result[0][12];
            te.user_free_time = result[0][13];
            te.user_identify = result[0][14];
            return te;
        }

        public DataTable GetTecinfo4Contact(string no, string spell, string sex, string dep,
            List<string> course_name_list, List<string> grade_name_list)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT distinct tec_info.tec_info_id 教员流水,tec_info.tec_info_no 编号,tec_info.tec_info_name 姓名,")
                .Append("tec_info.tec_info_course 可教课程,tec_info.tec_info_grade 可教年级,tec_info.tec_info_free_string 上课时间,")
                .Append("tec_info.tec_info_spell 拼音简写, tec_info.tec_info_sex 性别,  tec_info.tec_info_gddh 固定电话,")
                .Append("tec_info.tec_info_phone 手机号码,  tec_info.tec_info_address 家庭地址,tec_info.tec_info_right 权限,")
                .Append("tec_info.tec_info_regedit 注册时间,tec_info.tec_info_dep 分校,tec_info.tec_info_bz 备注 ")
                .Append("FROM tec_info,tec_arrange ")
                .Append("WHERE  tec_info.tec_info_id =  tec_arrange.tec_info_id ");
            List<SqlParameter> sqlParamsList = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(no))
            {
                sb.Append(" and tec_info.tec_info_no  like '%" + no + "%' ");
            }
            if (!string.IsNullOrEmpty(spell))
            {
                sb.Append(" and tec_info.tec_info_spell like '%@tec_info_spell' ");
                sqlParamsList.Add(new SqlParameter("@tec_info_spell", spell));
            }
            if (!string.IsNullOrEmpty(sex))
            {
                sb.Append(" and tec_info.tec_info_sex=@tec_info_sex ");
                sqlParamsList.Add(new SqlParameter("@tec_info_sex", sex));
            }
            if (!string.IsNullOrEmpty(dep))
            {
                sb.Append(" and tec_info.tec_info_dep=@dep_info_name ");
                sqlParamsList.Add(new SqlParameter("@dep_info_name", dep));
            }
            if (course_name_list != null && course_name_list.Count > 0)
            {
                sb.Append(" and (");
                foreach (string course_name in course_name_list)
                {
                    sb.Append(" tec_arrange.course_info_name='" + course_name + "' or");
                }
                sb.Append(" 1=0) ");
            }
            if (grade_name_list != null && grade_name_list.Count > 0)
            {
                sb.Append(" and (");
                foreach (string grade_name in grade_name_list)
                {
                    sb.Append(" grade_info_name='" + grade_name + "' or");
                }
                sb.Append(" 1=0)");
            }
            return DBExecuteUtil.querySqlTable(sb.ToString(), sqlParamsList.ToArray());
        }

        public DataTable GetTecinfo(string no, string spell, string sex, string dep, List<string> course_name_list, List<string> grade_name_list, string time)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT distinct tec_info.tec_info_id 教员流水,tec_info.tec_info_no 编号,tec_info.tec_info_name 姓名,")
                .Append("tec_info.tec_info_course 可教课程,tec_info.tec_info_grade 可教年级,tec_info.tec_info_free_string 上课时间,")
                .Append("tec_info.tec_info_spell 拼音简写, tec_info.tec_info_sex 性别,  tec_info.tec_info_gddh 固定电话,")
                .Append("tec_info.tec_info_phone 手机号码,  tec_info.tec_info_address 家庭地址,tec_info.tec_info_right 权限,")
                .Append("tec_info.tec_info_regedit 注册时间,tec_info.tec_info_dep 分校,tec_info.tec_info_bz 备注 ")
                .Append("FROM tec_info,tec_arrange ,zap2 ")
                .Append("WHERE  tec_info.tec_info_id =  tec_arrange.tec_info_id and tec_info.tec_info_id=zap2.zap_sb_id ");
            List<SqlParameter> sqlParamsList = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(no))
            {
                sb.Append(" and tec_info.tec_info_no  like '%" + no + "%' ");
            }
            if (!string.IsNullOrEmpty(spell))
            {
                sb.Append(" and tec_info.tec_info_spell like '%@tec_info_spell' ");
                sqlParamsList.Add(new SqlParameter("@tec_info_spell", spell));
            }
            if (!string.IsNullOrEmpty(sex))
            {
                sb.Append(" and tec_info.tec_info_sex=@tec_info_sex ");
                sqlParamsList.Add(new SqlParameter("@tec_info_sex", sex));
            }
            if (!string.IsNullOrEmpty(dep))
            {
                sb.Append(" and tec_info.tec_info_dep=@dep_info_name ");
                sqlParamsList.Add(new SqlParameter("@dep_info_name", dep));
            }
            if (course_name_list != null && course_name_list.Count > 0)
            {
                sb.Append(" and (");
                foreach (string course_name in course_name_list)
                {
                    sb.Append(" tec_arrange.course_info_name='" + course_name + "' or");
                }
                sb.Append(" 1=0) ");
            }
            if (grade_name_list != null && grade_name_list.Count > 0)
            {
                sb.Append(" and (");
                foreach (string grade_name in grade_name_list)
                {
                    sb.Append(" grade_info_name='" + grade_name + "' or");
                }
                sb.Append(" 1=0)");
            }
            if (!string.IsNullOrEmpty(time))
            {
                sb.Append("AND " + time + "='√'");
            }
            return DBExecuteUtil.querySqlTable(sb.ToString(), sqlParamsList.ToArray());
        }

        public TecArrangeEntity GetTecArrangeEntity(string id)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT * FROM (select tec_info_id 教员信息流水,tec_info_dep 分校编号,tec_info_no 教员编号,tec_info_name 教员姓名, ")
                .Append("tec_info_course 课程,tec_info_spell 拼音简写,tec_info_sex 性别,tec_info_grade 年级,tec_info_gddh 固定电话,   ")
                .Append("tec_info_phone 手机号码,tec_info_address 家庭住址,tec_info_identify 证件,  ")
                .Append("tec_info_dep 分校,tec_info_bz 备注,ISNULL(lsap_km,'无') 科目 ,lsap_kcsj 课程时间,")
                .Append("(select stu_info_name from stu_info where stu_info_no=lsap_xybh)学员姓名,LSAP_BZ 课程备注 ")
                .Append("from tec_info left join lsap  ")
                .Append("on tec_info_no=lsap_jybh AND LSAP_ZT='正在授课')A ")
                .Append("WHERE A.教员信息流水=" + id);
            DataTable dt = DBExecuteUtil.querySqlTable(sb.ToString());
            TecArrangeEntity tecArrangeEntity = new TecArrangeEntity();
            tecArrangeEntity.user_id = int.Parse(dt.Rows[0]["教员信息流水"].ToString());
            tecArrangeEntity.user_no = dt.Rows[0]["教员编号"].ToString();
            tecArrangeEntity.user_name = dt.Rows[0]["教员姓名"].ToString();
            tecArrangeEntity.user_dep = dt.Rows[0]["分校编号"].ToString();
            tecArrangeEntity.user_course = dt.Rows[0]["课程"].ToString();
            tecArrangeEntity.user_spell = dt.Rows[0]["拼音简写"].ToString();
            tecArrangeEntity.user_sex = dt.Rows[0]["性别"].ToString();
            tecArrangeEntity.user_grade = dt.Rows[0]["年级"].ToString();
            tecArrangeEntity.user_gddh = dt.Rows[0]["固定电话"].ToString();
            tecArrangeEntity.user_phone = dt.Rows[0]["手机号码"].ToString();
            tecArrangeEntity.user_address = dt.Rows[0]["家庭住址"].ToString();
            tecArrangeEntity.user_identify = dt.Rows[0]["证件"].ToString();
            tecArrangeEntity.user_depname = dt.Rows[0]["分校"].ToString();
            tecArrangeEntity.course_info = new List<CourseEntity>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i]["科目"].ToString() == "无") continue;
                tecArrangeEntity.course_info.Add(
                        new CourseEntity()
                        {
                            course_name = dt.Rows[i]["科目"].ToString(),
                            course_time = dt.Rows[i]["课程时间"].ToString(),
                            stu_name = dt.Rows[i]["学员姓名"].ToString(),
                            course_bz = dt.Rows[i]["课程备注"].ToString()
                        }
                    );
            }
            return tecArrangeEntity;
        }

        public List<TecArrangeEntity> GetTecArrangeList()
        {
            List<TecArrangeEntity> tecArrangeList = new List<TecArrangeEntity>();
            DataTable dt = GetTecStatistics();
            if (null == dt) return null;
            TecArrangeEntity tae;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                tae = GetTecArrangeEntity(dt.Rows[i]["教师流水编号"].ToString());
                //tae = new TecArrangeEntity();
                //tae.user_name = dt.Rows[i]["教师姓名"].ToString();
                //tae.user_no = dt.Rows[i]["教师编号"].ToString();
                tae.stuEntity = new StuEntity()
                    {
                        user_name = dt.Rows[i]["学生姓名"].ToString(),
                        user_no = dt.Rows[i]["学生编号"].ToString()
                    };
                tae.courseEntity = new CourseEntity()
                    {
                        course_name = dt.Rows[i]["科目"].ToString(),
                        course_time = dt.Rows[i]["时间"].ToString()
                    };
                //tae.user_bz = dt.Rows[i]["备注"].ToString();
                tecArrangeList.Add(tae);
            }
            return tecArrangeList;
        }

        public List<TecArrangeEntity> GetTecArrlist4Zmap(TecEntity tecEntity)
        {
            List<TecArrangeEntity> tecArrangeList = new List<TecArrangeEntity>();
            DataTable dt = GetTecStatistics(tecEntity);
            if (null == dt) return null;
            TecArrangeEntity tae;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                tae = GetTecArrangeEntity(dt.Rows[i]["教师流水编号"].ToString());
                //tae = new TecArrangeEntity();
                //tae.user_name = dt.Rows[i]["教师姓名"].ToString();
                //tae.user_no = dt.Rows[i]["教师编号"].ToString();
                tae.stuEntity = new StuEntity()
                {
                    user_name = dt.Rows[i]["学生姓名"].ToString(),
                    user_no = dt.Rows[i]["学生编号"].ToString()
                };
                tae.courseEntity = new CourseEntity()
                {
                    course_name = dt.Rows[i]["科目"].ToString(),
                    course_time = dt.Rows[i]["时间"].ToString()
                };
                //tae.user_bz = dt.Rows[i]["备注"].ToString();
                tecArrangeList.Add(tae);
            }
            return tecArrangeList;
        }
        private DataTable GetTecStatistics(TecEntity tecEntity)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" SELECT TEC_INFO_NAME 教师姓名,TEC_INFO_NO 教师编号,TEC_INFO_ID 教师流水编号,ISNULL(STU_INFO_NAME,'')学生姓名,ISNULL(STU_INFO_NO,0)学生编号,  ");
            sb.Append(" ISNULL(LSAP_KM,'')科目,ISNULL(LSAP_KCSJ,'')时间,ISNULL(LSAP_BZ,0)备注  ");
            sb.Append(" FROM TEC_INFO LEFT JOIN  ");
            sb.Append(" (SELECT LSAP_KM,LSAP_KCSJ,LSAP_XYBH,LSAP_JYBH,LSAP_BZ FROM LSAP WHERE LSAP_ZT='正在授课')AS L ON L.LSAP_JYBH=TEC_INFO_NO  ");
            sb.Append(" LEFT JOIN STU_INFO ON L.LSAP_XYBH=STU_INFO_NO   ");
            if (!string.IsNullOrEmpty(tecEntity.user_spell))
            {
                sb.Append(" where tec_info.tec_info_spell like '%" + tecEntity.user_spell + "' ");
            }
            return DBExecuteUtil.querySqlTable(sb.ToString());
        }

        public DataTable GetTec8Entity(TecEntity tecEntity)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT distinct tec_info.tec_info_id 教员流水,tec_info_no 教员编号,tec_info.tec_info_name 姓名,")
                .Append("tec_info.tec_info_course 可教课程,tec_info.tec_info_grade 可教年级,tec_info.tec_info_free_string 上课时间,")
                .Append("tec_info.tec_info_spell 拼音简写, tec_info.tec_info_sex 性别,  tec_info.tec_info_gddh 固定电话,")
                .Append("tec_info.tec_info_phone 手机号码,  tec_info.tec_info_address 家庭地址,tec_info.tec_info_right 权限,")
                .Append("tec_info.tec_info_regedit 注册时间,tec_info.tec_info_dep 分校,tec_info.tec_info_bz 备注 ")
                .Append("FROM tec_info,ZAP2 WHERE TEC_INFO.TEC_INFO_ID=ZAP2.ZAP_SB_ID ");
            if (!string.IsNullOrEmpty(tecEntity.user_spell))
            {
                sb.Append(" and tec_info.tec_info_spell like '%" + tecEntity.user_spell + "' ");
            }
            return DBExecuteUtil.querySqlTable(sb.ToString());
        }

        public DataTable GetTecStatistics()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" SELECT TEC_INFO_NAME 教师姓名,TEC_INFO_NO 教师编号,TEC_INFO_ID 教师流水编号,ISNULL(STU_INFO_NAME,'')学生姓名,ISNULL(STU_INFO_NO,0)学生编号,  ");
            sb.Append(" ISNULL(LSAP_KM,'')科目,ISNULL(LSAP_KCSJ,'')时间,ISNULL(LSAP_BZ,0)备注  ");
            sb.Append(" FROM TEC_INFO LEFT JOIN  ");
            sb.Append(" (SELECT LSAP_KM,LSAP_KCSJ,LSAP_XYBH,LSAP_JYBH,LSAP_BZ FROM LSAP WHERE LSAP_ZT='正在授课')AS L ON L.LSAP_JYBH=TEC_INFO_NO  ");
            sb.Append(" LEFT JOIN STU_INFO ON L.LSAP_XYBH=STU_INFO_NO   ");
            return DBExecuteUtil.querySqlTable(sb.ToString());
        }

        public DataTable GetTecinfo8Time(List<string> timeseclist)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT * FROM TEC_INFO,ZAP  ");
            sb.Append("WHERE TEC_INFO_ID=ZAP_SB_ID");
            for (int i = 0; i < timeseclist.Count; i++)
            {
                sb.Append("AND " + timeseclist + "='√'");
            }
            return DBExecuteUtil.querySqlTable(sb.ToString());
        }

        #endregion

        #region 教师执行

        /// <summary>
        /// 通过id修改密码
        /// </summary>
        /// <param name="id"></param>
        /// <param name="psw"></param>
        /// <returns></returns>
        public int ModiPsw8id(int id, string psw)
        {
            StringBuilder sb = new StringBuilder("update tec_info set ")
                .Append("tec_info_psw=@tec_info_psw where tec_info_id=@tec_info_id");
            SqlParameter[] sqlParams = new SqlParameter[]{
                new SqlParameter("@tec_info_psw",psw),
                new SqlParameter("@tec_info_id",id)
            };
            return DBExecuteUtil.executeSqlWithPar(sb.ToString(), sqlParams);
        }

        public int addTecinfo(TecEntity tecEntity)
        {
            StringBuilder sb = new StringBuilder("insert into tec_info( ")
                .Append("dep_info_id,tec_info_name,tec_info_spell,tec_info_psw,tec_info_sex, ")
                .Append("tec_info_gddh,tec_info_phone,tec_info_address,tec_info_identify,tec_info_right, ")
                .Append("tec_info_regedit,tec_info_dep,tec_info_bz,tec_info_free_string,tec_info_course,tec_info_grade, ")
                .Append("tec_info_time_free,tec_info_time_busy ")
                .Append(") values(")
                .Append("@dep_info_id,@tec_info_name,@tec_info_spell,@tec_info_psw,@tec_info_sex, ")
                .Append("@tec_info_gddh,@tec_info_phone,@tec_info_address,@tec_info_identify,@tec_info_right,")
                .Append("@tec_info_regedit,@tec_info_dep,@tec_info_bz,@tec_info_free_string,@tec_info_course,@tec_info_grade, ")
                .Append("@tec_info_time_free,'' ")
                .Append(") select SCOPE_IDENTITY()");
            SqlParameter[] sqlParams = new SqlParameter[]{
                new SqlParameter("@dep_info_id",tecEntity.user_dep),
                new SqlParameter("@tec_info_name",tecEntity.user_name),
                new SqlParameter("@tec_info_spell",tecEntity.user_spell),
                new SqlParameter("@tec_info_psw",tecEntity.user_psw),
                new SqlParameter("@tec_info_sex",tecEntity.user_sex),
                
                new SqlParameter("@tec_info_gddh",tecEntity.user_gddh??"0"),
                new SqlParameter("@tec_info_phone",tecEntity.user_phone??"0"),
                new SqlParameter("@tec_info_address",tecEntity.user_address),
                new SqlParameter("@tec_info_identify",tecEntity.user_identify),
                new SqlParameter("@tec_info_right",tecEntity.user_right),
                new SqlParameter("@tec_info_regedit",tecEntity.user_regedit),
                new SqlParameter("@tec_info_dep",tecEntity.user_depname),
                
                new SqlParameter("@tec_info_bz",tecEntity.user_bz),
                new SqlParameter("@tec_info_free_string",tecEntity.user_free_time),
                new SqlParameter("@tec_info_course",tecEntity.user_course),
                new SqlParameter("@tec_info_grade",tecEntity.user_grade),
                new SqlParameter("@tec_info_time_free",tecEntity.user_free_time_simple)
            };
            return DBExecuteUtil.executeSqlWithPar(sb.ToString(), sqlParams);
        }

        public void addTecarrange(TecArrangeEntity taEntity)
        {
            foreach (CourseEntity ce in taEntity.course_info)
            {
                foreach (GradeEntity ge in taEntity.grade_info)
                {
                    StringBuilder sb = new StringBuilder("insert into tec_arrange( ")
                        .Append("tec_info_id,course_info_id,course_info_name,grade_info_id,grade_info_name ")
                        .Append(") values( ")
                        .Append(" @tec_info_id,@course_info_id,@course_info_name, ")
                        .Append("@grade_info_id,@grade_info_name)");
                    SqlParameter[] sqlParams = new SqlParameter[]{
                            new SqlParameter("@tec_info_id",taEntity.tec_info_id),
                            new SqlParameter("@course_info_id",ce.course_id),
                            new SqlParameter("@course_info_name",ce.course_name),
                            new SqlParameter("@grade_info_id",ge.grade_id),
                            new SqlParameter("@grade_info_name",ge.grade_name)
                        };
                    DBExecuteUtil.executeSqlWithPar(sb.ToString(), sqlParams);
                }
            }
        }

        public void delTecarrange(string id)
        {
            string sql = "delete from tec_arrange where tec_info_id=" + id;
            DBExecuteUtil.executeSql(sql);
        }

        public void updateTecinfo(TecEntity tecEntity)
        {
            StringBuilder sb = new StringBuilder("update tec_info set ")
                .Append("dep_info_id=@dep_info_id,tec_info_name=@tec_info_name,")
                .Append("tec_info_spell=@tec_info_spell,tec_info_psw=@tec_info_psw,tec_info_sex=@tec_info_sex, ")
                .Append("tec_info_gddh=@tec_info_gddh,tec_info_phone=@tec_info_phone,")
                .Append("tec_info_address=@tec_info_address,tec_info_identify=@tec_info_identify,tec_info_right=@tec_info_right, ")
                .Append("tec_info_regedit=@tec_info_regedit,tec_info_dep=@tec_info_dep,tec_info_bz=@tec_info_bz,")
                .Append("tec_info_free_string=@tec_info_free_string,tec_info_course=@tec_info_course,tec_info_grade=@tec_info_grade, ")
                .Append("tec_info_time_free=@tec_info_time_free,tec_info_time_busy=@tec_info_time_busy ")
                .Append("where tec_info_id=@tec_info_id ");
            SqlParameter[] sqlParams = new SqlParameter[]{
                new SqlParameter("@dep_info_id",tecEntity.user_dep),
                new SqlParameter("@tec_info_name",tecEntity.user_name),
                new SqlParameter("@tec_info_spell",tecEntity.user_spell),
                new SqlParameter("@tec_info_psw",tecEntity.user_psw),
                new SqlParameter("@tec_info_sex",tecEntity.user_sex),
                
                new SqlParameter("@tec_info_gddh",tecEntity.user_gddh),
                new SqlParameter("@tec_info_phone",tecEntity.user_phone),
                new SqlParameter("@tec_info_address",tecEntity.user_address),
                new SqlParameter("@tec_info_identify",tecEntity.user_identify),
                new SqlParameter("@tec_info_right",tecEntity.user_right),
                new SqlParameter("@tec_info_regedit",tecEntity.user_regedit),
                new SqlParameter("@tec_info_dep",tecEntity.user_depname),
                
                new SqlParameter("@tec_info_bz",tecEntity.user_bz),
                new SqlParameter("@tec_info_free_string",tecEntity.user_free_time),
                new SqlParameter("@tec_info_course",tecEntity.user_course),
                new SqlParameter("@tec_info_grade",tecEntity.user_grade),
                new SqlParameter("@tec_info_id",tecEntity.user_id),
                new SqlParameter("@tec_info_time_free",tecEntity.user_free_time_simple),
                new SqlParameter("@tec_info_time_busy",tecEntity.user_course_time_simple??"")
            };
            DBExecuteUtil.executeSqlWithPar(sb.ToString(), sqlParams);
        }

        #endregion

        #region 学生查询

        public DataTable GetStuInfo(string zt)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select stu_info_id 学员信息流水,dep_info_id 分校编号,stu_info_no 学员编号,stu_info_name 学员姓名,")
                .Append("stu_info_lxr 联系人,stu_info_spell 拼音简写,stu_info_sex 性别,stu_info_grade 年级,stu_info_gddh 固定电话, ")
                .Append("stu_info_phone 手机号码,stu_info_identify 证件,stu_info_course 课程,stu_info_dep 分校,stu_info_arranged 已安排,")
                .Append("stu_info_time_use 已用课时,stu_info_time_total 总课时,stu_info_time_ever 累计课时,stu_info_freetime 上课时间,stu_info_address 家庭住址,")
                .Append("stu_info_bz 备注 ")
                .Append("from stu_info ");
            if (!string.IsNullOrEmpty(zt))
                sb.Append(" where stu_info_arranged='" + zt + "'");
            return DBExecuteUtil.querySqlTable(sb.ToString());
        }

        public DataTable GetStuInfo8id(string id)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select stu_info_id 学员信息流水,dep_info_id 分校编号,stu_info_no 学员编号,stu_info_name 学员姓名,")
                .Append("stu_info_lxr 联系人,stu_info_spell 拼音简写,stu_info_sex 性别,stu_info_grade 年级,stu_info_gddh 固定电话, ")
                .Append("stu_info_phone 手机号码,stu_info_address 家庭住址,stu_info_identify 证件,")
                .Append("stu_info_course 课程,stu_info_dep 分校,stu_info_freetime 上课时间,stu_info_arranged 已安排,")
                .Append("stu_info_time_use 已用课时,stu_info_time_total 总课时,stu_info_time_ever 累计课时,")
                .Append("stu_info_bz 备注 ")
                .Append("from stu_info where stu_info_id=" + id);
            return DBExecuteUtil.querySqlTable(sb.ToString());
        }
        public DataTable GetStuInfo8no(string tmpno, string time)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select stu_info_id 学员信息流水,dep_info_id 分校编号,stu_info_no 学员编号,stu_info_name 学员姓名,")
                .Append("stu_info_lxr 联系人,stu_info_spell 拼音简写,stu_info_sex 性别,stu_info_grade 年级,stu_info_gddh 固定电话, ")
                .Append("stu_info_phone 手机号码,stu_info_address 家庭住址,stu_info_identify 证件,")
                .Append("stu_info_course 课程,stu_info_dep 分校,stu_info_freetime 上课时间,stu_info_arranged 已安排,")
                .Append("stu_info_time_use 已用课时,stu_info_time_total 总课时,stu_info_time_ever 累计课时,")
                .Append("stu_info_bz 备注 ")
                .Append("from stu_info ");
            if (!string.IsNullOrEmpty(tmpno))
                sb.Append(" where stu_info_no like '%" + tmpno + "%'");
            return DBExecuteUtil.querySqlTable(sb.ToString());
        }

        public DataTable GetStuArrangeInfo(string id)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT * FROM (select stu_info_id 学员信息流水,dep_info_id 分校编号,stu_info_no 学员编号,stu_info_name 学员姓名,")
                .Append("stu_info_lxr 联系人,stu_info_spell 拼音简写,stu_info_sex 性别,stu_info_grade 年级,stu_info_gddh 固定电话, ")
                .Append("stu_info_phone 手机号码,stu_info_address 家庭住址,stu_info_identify 证件,")
                .Append("stu_info_course 课程,stu_info_dep 分校,stu_info_freetime 上课时间,stu_info_arranged 已安排,")
                .Append("stu_info_time_use 已用课时,stu_info_time_total 总课时,stu_info_time_ever 累计课时,")
                .Append("stu_info_bz 备注,ISNULL(lsap_km,'无') 科目 ,lsap_kcsj 课程时间,(select tec_info_name from tec_info where tec_info_no=lsap_Jybh)教员姓名,LSAP_BZ 课程备注  ")
                .Append("from stu_info left join lsap  ")
                .Append("on stu_info_no=lsap_xybh AND LSAP_ZT='正在授课')A ");
            if (!string.IsNullOrEmpty(id))
                sb.Append("WHERE A.学员信息流水=" + id);
            return DBExecuteUtil.querySqlTable(sb.ToString());
        }

        public DataTable GetStuStatistics()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" SELECT STU_INFO_NAME 学生姓名,STU_INFO_NO 学生编号,STU_INFO_ID 学员信息流水,ISNULL(TEC_INFO_NAME,'')教师姓名,ISNULL(TEC_INFO_ID,0)教师ID,ISNULL(LSAP_KM,'')科目,ISNULL(LSAP_KCSJ,'')时间,ISNULL(LSAP_BZ,'')备注 ");
            sb.Append(" FROM STU_INFO LEFT JOIN  ");
            sb.Append(" (SELECT LSAP_KM,LSAP_KCSJ,LSAP_XYBH,LSAP_JYBH,LSAP_BZ FROM LSAP WHERE LSAP_ZT='正在授课')AS L ON L.LSAP_XYBH=STU_INFO_NO ");
            sb.Append(" LEFT JOIN TEC_INFO ON L.LSAP_JYBH=TEC_INFO_NO ORDER BY STU_INFO_NO ");
            return DBExecuteUtil.querySqlTable(sb.ToString());
        }

        private DataTable GetStuStatistics(StuEntity stuEntity)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" SELECT STU_INFO_NAME 学生姓名,STU_INFO_NO 学生编号,STU_INFO_ID 学员信息流水,ISNULL(TEC_INFO_NAME,'')教师姓名,ISNULL(TEC_INFO_ID,0)教师ID,ISNULL(LSAP_KM,'')科目,ISNULL(LSAP_KCSJ,'')时间,ISNULL(LSAP_BZ,'')备注 ");
            sb.Append(" FROM STU_INFO LEFT JOIN  ");
            sb.Append(" (SELECT LSAP_KM,LSAP_KCSJ,LSAP_XYBH,LSAP_JYBH,LSAP_BZ FROM LSAP WHERE LSAP_ZT='正在授课')AS L ON L.LSAP_XYBH=STU_INFO_NO ");
            sb.Append(" LEFT JOIN TEC_INFO ON L.LSAP_JYBH=TEC_INFO_NO  ");
            if (!string.IsNullOrEmpty(stuEntity.user_spell))
                sb.Append(" where stu_info_spell like '%" + stuEntity.user_spell + "'");

            sb.Append(" ORDER BY STU_INFO_NO ");
            return DBExecuteUtil.querySqlTable(sb.ToString());
        }

        public DataTable GetStu8Entity(StuEntity stuEntity)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select stu_info_id 学员信息流水,dep_info_id 分校编号,stu_info_no 学员编号,stu_info_name 学员姓名,")
                .Append("stu_info_lxr 联系人,stu_info_spell 拼音简写,stu_info_sex 性别,stu_info_grade 年级,stu_info_gddh 固定电话, ")
                .Append("stu_info_phone 手机号码,stu_info_identify 证件,stu_info_course 课程,stu_info_dep 分校,stu_info_arranged 已安排,")
                .Append("stu_info_time_use 已用课时,stu_info_time_total 总课时,stu_info_time_ever 累计课时,stu_info_freetime 上课时间,stu_info_address 家庭住址,")
                .Append("stu_info_bz 备注 ")
                .Append("from stu_info ");
            if (!string.IsNullOrEmpty(stuEntity.user_spell))
                sb.Append(" where stu_info_spell like '%" + stuEntity.user_spell + "'");
            return DBExecuteUtil.querySqlTable(sb.ToString());
        }

        /// <summary>
        /// 根据条件获得学生基本信息
        /// </summary>
        /// <param name="course_name_list"></param>
        /// <param name="grade_name_list"></param>
        /// <param name="dep_name"></param>
        /// <param name="time_sec_list"></param>
        /// <returns></returns>
        public DataTable GetStuinfo8condi(string no, string spell, string sex, string dep, List<string> course_name_list, List<string> grade_name_list, List<string> time_sec_list,
            bool isarranged)//,string time1,string time2)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select stu_info_id 学员信息流水,dep_info_id 分校编号,stu_info_no 学员编号,stu_info_name 学员姓名,")
                .Append("stu_info_lxr 联系人,stu_info_spell 拼音简写,stu_info_sex 性别,stu_info_grade 年级,stu_info_gddh 固定电话, ")
                .Append("stu_info_phone 手机号码,stu_info_address 家庭住址,stu_info_identify 证件,")
                .Append("stu_info_course 课程,stu_info_dep 分校,stu_info_freetime 上课时间,stu_info_arranged 已安排,")
                .Append("stu_info_time_use 已用课时,stu_info_time_total 总课时,stu_info_time_ever 累计课时,")
                .Append("stu_info_bz 备注 ")
                .Append("from stu_info where 1=1 ");
            List<SqlParameter> sqlParamsList = new List<SqlParameter>();
            if (!isarranged)
                sb.Append(" and stu_info_arranged='未安排' ");
            if (!string.IsNullOrEmpty(no))
            {
                sb.Append(" and stu_info_no like '%" + no + "%'  ");
            }
            if (!string.IsNullOrEmpty(spell))
            {
                sb.Append(" and stu_info_spell like '%@stu_info_spell%' ");
                sqlParamsList.Add(new SqlParameter("@stu_info_spell", spell));
            }
            if (!string.IsNullOrEmpty(sex))
            {
                sb.Append(" and stu_info_sex=@stu_info_sex ");
                sqlParamsList.Add(new SqlParameter("@stu_info_sex", sex));
            }
            if (!string.IsNullOrEmpty(dep))
            {
                sb.Append(" and stu_info_dep=@stu_info_dep ");
                sqlParamsList.Add(new SqlParameter("@stu_info_dep", dep));
            }
            if (course_name_list != null && course_name_list.Count > 0)
            {
                sb.Append(" and (");
                foreach (string course_name in course_name_list)
                {
                    sb.Append(" stu_info_course like '%[" + course_name + "]%' or");
                }
                sb.Append(" 1=0) ");
            }
            if (grade_name_list != null && grade_name_list.Count > 0)
            {
                sb.Append(" and (");
                foreach (string grade_name in grade_name_list)
                {
                    sb.Append(" grade_info_name like '%[" + grade_name + "]%' or");
                }
                sb.Append(" 1=0)");
            }
            return DBExecuteUtil.querySqlTable(sb.ToString(), sqlParamsList.ToArray());
        }

        public StuEntity GetStuinfoEntity(string id)
        {
            DataTable dt = GetStuInfo8id(id);
            StuEntity stuEntity = new StuEntity();
            stuEntity.user_id = int.Parse(dt.Rows[0]["学员信息流水"].ToString());
            stuEntity.user_dep = dt.Rows[0]["分校编号"].ToString();
            stuEntity.user_depname = dt.Rows[0]["分校"].ToString();
            stuEntity.user_no = dt.Rows[0]["学员编号"].ToString();
            stuEntity.user_name = dt.Rows[0]["学员姓名"].ToString();
            stuEntity.user_spell = dt.Rows[0]["拼音简写"].ToString();
            stuEntity.user_sex = dt.Rows[0]["性别"].ToString();
            stuEntity.user_grade = dt.Rows[0]["年级"].ToString();
            stuEntity.user_gddh = dt.Rows[0]["固定电话"].ToString();
            stuEntity.user_phone = dt.Rows[0]["手机号码"].ToString();
            stuEntity.user_address = dt.Rows[0]["家庭住址"].ToString();
            stuEntity.user_identify = dt.Rows[0]["证件"].ToString();
            stuEntity.user_course = dt.Rows[0]["课程"].ToString();
            stuEntity.user_course_time = dt.Rows[0]["上课时间"].ToString();
            stuEntity.user_arranged = dt.Rows[0]["已安排"].ToString();
            stuEntity.user_course_use = int.Parse(dt.Rows[0]["已用课时"].ToString());
            stuEntity.user_course_total = int.Parse(dt.Rows[0]["总课时"].ToString());
            stuEntity.user_course_ever = int.Parse(dt.Rows[0]["累计课时"].ToString());
            stuEntity.user_bz = dt.Rows[0]["备注"].ToString();
            stuEntity.user_lxr = dt.Rows[0]["联系人"].ToString();
            return stuEntity;
        }

        public StuArrangeEntity GetStuArrangeEntity(string id)
        {
            DataTable dt = this.GetStuArrangeInfo(id);
            StuArrangeEntity stuArrangeEntity = new StuArrangeEntity();
            stuArrangeEntity.user_id = int.Parse(dt.Rows[0]["学员信息流水"].ToString());
            stuArrangeEntity.user_dep = dt.Rows[0]["分校编号"].ToString();
            stuArrangeEntity.user_depname = dt.Rows[0]["分校"].ToString();
            stuArrangeEntity.user_no = dt.Rows[0]["学员编号"].ToString();
            stuArrangeEntity.user_name = dt.Rows[0]["学员姓名"].ToString();
            stuArrangeEntity.user_spell = dt.Rows[0]["拼音简写"].ToString();
            stuArrangeEntity.user_sex = dt.Rows[0]["性别"].ToString();
            stuArrangeEntity.user_grade = dt.Rows[0]["年级"].ToString();
            stuArrangeEntity.user_gddh = dt.Rows[0]["固定电话"].ToString();
            stuArrangeEntity.user_phone = dt.Rows[0]["手机号码"].ToString();
            stuArrangeEntity.user_address = dt.Rows[0]["家庭住址"].ToString();
            stuArrangeEntity.user_identify = dt.Rows[0]["证件"].ToString();
            stuArrangeEntity.user_course = dt.Rows[0]["科目"].ToString();
            stuArrangeEntity.user_course_time = dt.Rows[0]["课程时间"].ToString();
            stuArrangeEntity.user_arranged = dt.Rows[0]["已安排"].ToString();
            stuArrangeEntity.user_course_use = int.Parse(dt.Rows[0]["已用课时"].ToString());
            stuArrangeEntity.user_course_total = int.Parse(dt.Rows[0]["总课时"].ToString());
            stuArrangeEntity.user_course_ever = int.Parse(dt.Rows[0]["累计课时"].ToString());
            stuArrangeEntity.user_bz = dt.Rows[0]["备注"].ToString();
            stuArrangeEntity.user_lxr = dt.Rows[0]["联系人"].ToString();
            List<CourseEntity> clist = new List<CourseEntity>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i]["科目"].ToString() == "无") continue;
                clist.Add(new CourseEntity()
                {
                    course_name = dt.Rows[i]["科目"].ToString(),
                    course_time = dt.Rows[i]["课程时间"].ToString(),
                    tec_name = dt.Rows[i]["教员姓名"].ToString(),
                    stu_name = dt.Rows[i]["学员姓名"].ToString(),
                    course_bz = dt.Rows[i]["课程备注"].ToString()
                });
            }
            stuArrangeEntity.courseEntityList = clist;
            return stuArrangeEntity;
        }

        public List<StuArrangeEntity> GetStuArrangeList()
        {
            List<StuArrangeEntity> stuArrangeList = new List<StuArrangeEntity>();
            DataTable dt = GetStuStatistics();
            if (dt == null) return null;
            StuArrangeEntity stuArrangeEntity;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                stuArrangeEntity = GetStuArrangeEntity(dt.Rows[i]["学员信息流水"].ToString());
                //stuArrangeEntity = new StuArrangeEntity();
                //stuArrangeEntity.user_name = dt.Rows[i]["学生姓名"].ToString();
                //stuArrangeEntity.user_no = dt.Rows[i]["学生编号"].ToString();

                stuArrangeEntity.tecEntity = new TecEntity()
                {
                    user_name = dt.Rows[i]["教师姓名"].ToString(),
                    user_id = int.Parse(dt.Rows[i]["教师ID"].ToString())
                };
                stuArrangeEntity.courseEntity = new CourseEntity()
                    {
                        course_name = dt.Rows[i]["科目"].ToString(),
                        course_time = dt.Rows[i]["时间"].ToString()
                    };
                //stuArrangeEntity.user_bz = dt.Rows[i]["备注"].ToString();
                stuArrangeList.Add(stuArrangeEntity);
            }
            return stuArrangeList;
        }

        public List<StuArrangeEntity> GetStuArrlist4Zmap(StuEntity stuEntity)
        {
            List<StuArrangeEntity> stuArrangeList = new List<StuArrangeEntity>();
            DataTable dt = GetStuStatistics(stuEntity);
            if (dt == null) return null;
            StuArrangeEntity stuArrangeEntity;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                stuArrangeEntity = GetStuArrangeEntity(dt.Rows[i]["学员信息流水"].ToString());
                //stuArrangeEntity = new StuArrangeEntity();
                //stuArrangeEntity.user_name = dt.Rows[i]["学生姓名"].ToString();
                //stuArrangeEntity.user_no = dt.Rows[i]["学生编号"].ToString();

                stuArrangeEntity.tecEntity = new TecEntity()
                {
                    user_name = dt.Rows[i]["教师姓名"].ToString(),
                    user_id = int.Parse(dt.Rows[i]["教师ID"].ToString())
                };
                stuArrangeEntity.courseEntity = new CourseEntity()
                {
                    course_name = dt.Rows[i]["科目"].ToString(),
                    course_time = dt.Rows[i]["时间"].ToString()
                };
                //stuArrangeEntity.user_bz = dt.Rows[i]["备注"].ToString();
                stuArrangeList.Add(stuArrangeEntity);
            }
            return stuArrangeList;
        }

        #endregion

        #region 学生执行

        /// <summary>
        /// 添加学生信息
        /// </summary>
        /// <param name="stuEntity"></param>
        /// <returns></returns>
        public int addStuinfo(StuEntity stuEntity)
        {
            StringBuilder sb = new StringBuilder("insert into stu_info( ")
                .Append("stu_info_name,stu_info_spell,stu_info_sex,stu_info_gddh, ")
                .Append("stu_info_phone,stu_info_address,stu_info_grade,stu_info_course, ")
                .Append("stu_info_freetime,stu_info_time_use,stu_info_time_total, ")
                .Append("stu_info_time_ever,stu_info_arranged,stu_info_bz,dep_info_id,")
                .Append("stu_info_dep,stu_info_regedit,stu_info_lxr)")
                .Append(" values(")
                .Append(" @stu_info_name,@stu_info_spell,@stu_info_sex,@stu_info_gddh,")
                .Append("@stu_info_phone,@stu_info_address,@stu_info_grade,@stu_info_course, ")
                .Append("@stu_info_freetime,@stu_info_time_use,@stu_info_time_total, ")
                .Append("@stu_info_time_ever,@stu_info_arranged,@stu_info_bz,@dep_info_id,")
                .Append("@stu_info_dep,@stu_info_regedit,@stu_info_lxr)");
            SqlParameter[] sqlParams = new SqlParameter[]{
                new SqlParameter("@stu_info_name",stuEntity.user_name),
                new SqlParameter("@stu_info_spell",stuEntity.user_spell),
                new SqlParameter("@stu_info_sex",stuEntity.user_sex),
                new SqlParameter("@stu_info_gddh",stuEntity.user_gddh??"0"),

                new SqlParameter("@stu_info_phone",stuEntity.user_phone??"0"),
                new SqlParameter("@stu_info_address",stuEntity.user_address),
                new SqlParameter("@stu_info_grade",stuEntity.user_grade),
                new SqlParameter("@stu_info_course",stuEntity.user_course),
                new SqlParameter("@stu_info_freetime",stuEntity.user_course_time),

                new SqlParameter("@stu_info_time_use",stuEntity.user_course_use),
                new SqlParameter("@stu_info_time_total",stuEntity.user_course_total),
                new SqlParameter("@stu_info_time_ever",stuEntity.user_course_ever),
                new SqlParameter("@stu_info_arranged",stuEntity.user_arranged),
                new SqlParameter("@stu_info_bz",stuEntity.user_bz),

                new SqlParameter("@dep_info_id",stuEntity.user_dep),
                new SqlParameter("@stu_info_dep",stuEntity.user_depname),
                new SqlParameter("@stu_info_regedit",stuEntity.user_regedit),
                new SqlParameter("@stu_info_lxr",stuEntity.user_lxr)
            };
            return DBExecuteUtil.executeSqlWithPar(sb.ToString(), sqlParams);
        }

        public void updateStuinfo(StuEntity stuEntity)
        {
            StringBuilder sb = new StringBuilder("update stu_info set ")
                .Append("stu_info_name=@stu_info_name,stu_info_spell=@stu_info_spell, ")
                .Append("stu_info_sex=@stu_info_sex,stu_info_gddh=@stu_info_gddh,")
                .Append("stu_info_phone=@stu_info_phone,stu_info_address=@stu_info_address, ")
                .Append("stu_info_grade=@stu_info_grade,stu_info_course=@stu_info_course,")
                .Append("stu_info_freetime=@stu_info_freetime,stu_info_bz=@stu_info_bz,dep_info_id=@dep_info_id, ")
                .Append("stu_info_dep=@stu_info_dep where stu_info_id=@stu_info_id");
            SqlParameter[] sqlParams = new SqlParameter[]{
                new SqlParameter("@stu_info_name",stuEntity.user_name),
                new SqlParameter("@stu_info_spell",stuEntity.user_spell),
                new SqlParameter("@stu_info_sex",stuEntity.user_sex),
                new SqlParameter("@stu_info_gddh",stuEntity.user_gddh),

                new SqlParameter("@stu_info_phone",stuEntity.user_phone),
                new SqlParameter("@stu_info_address",stuEntity.user_address),
                new SqlParameter("@stu_info_grade",stuEntity.user_grade),
                new SqlParameter("@stu_info_course",stuEntity.user_course),
                new SqlParameter("@stu_info_freetime",stuEntity.user_course_time),

                new SqlParameter("@stu_info_bz",stuEntity.user_bz),
                new SqlParameter("@dep_info_id",stuEntity.user_dep),
                new SqlParameter("@stu_info_dep",stuEntity.user_depname),
                new SqlParameter("@stu_info_id",stuEntity.user_id)
            };
            DBExecuteUtil.executeSqlWithPar(sb.ToString(), sqlParams);
        }

        #endregion
    }
}
